﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace DatabaseService.DatabaseService
{
    public class MysqlDatabaseService : IDatabaseService
    {
        private string connStr;
        private int timeout = 2000;
        private object conn;
        private BaseCharset charset;

        public IDatabaseInfoDAO _databaseInfoDAO;
        /// <summary>
        /// 数据库基础设置
        /// </summary>
        public IDatabaseInfoDAO databaseInfoDAO
        {
            get => _databaseInfoDAO;
            set
            {
                _databaseInfoDAO = value;
                this.Init();
            }
        }

        #region 初始化数据

        public MysqlDatabaseService() { }

        public void Init()
        {
            connStr = databaseInfoDAO.GetConnectionString();
            timeout = databaseInfoDAO.GetTimeout();
            charset = databaseInfoDAO.GetCharset();
        }
        #endregion

        #region 增删改查

        public DataTable Select(string commandText, IDictionary<string, object> parameters = null)
        {
            DataTable result = new DataTable();
            try
            {
                //检查参数与sql匹配性
                CheckBusParameters(commandText, parameters);

                string sqlStr = charset.insertToDatabase(commandText);
                using (MySqlConnection connection = new MySqlConnection(connStr))
                {
                    connection.Open();
                    MySqlCommand MySqlCommand = new MySqlCommand(sqlStr, connection);
                    if (parameters != null && parameters.Count > 0)
                    {
                        //会报错，用下面的参数添加方式
                        //MySqlParameter[] MySqlParameters = parameters.Select(p => new MySqlParameter(p.Key, p.Value)).ToArray();
                        //MySqlCommand.Parameters.AddRange(MySqlParameters);
                        foreach (var item in parameters)
                        {
                            object value = item.Value;
                            if (item.Value is String)
                                value = charset.insertToDatabase(item.Value.ToString());
                            MySqlCommand.Parameters.Add(new MySqlParameter(item.Key, value));
                        }
                    }
                    MySqlDataAdapter MySqlDataAdapter = new MySqlDataAdapter(MySqlCommand);
                    MySqlDataAdapter.Fill(result);
                    MySqlDataAdapter.Dispose();
                    MySqlCommand.Dispose();
                }

                result = charset.getDataTableFromDatabase(result);
            }
            catch (MySqlException ex)
            {
                throw new Exception(charset.getFromDatabase(ex.Message.ToString()), ex);
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }

        public int ExecuteNonQuery(string commandText, IDictionary<string, object> parameters)
        {
            int ret = 0;
            try
            {
                //检查参数与sql匹配性
                CheckBusParameters(commandText, parameters);

                string sqlStr = charset.insertToDatabase(commandText);
                using (MySqlConnection connection = new MySqlConnection(connStr))
                {
                    connection.Open();
                    MySqlCommand MySqlCommand = new MySqlCommand(sqlStr, connection);
                    if (parameters != null && parameters.Count > 0)
                    {
                        foreach (var item in parameters)
                        {
                            object value = item.Value;
                            if (item.Value is String)
                                value = charset.insertToDatabase(item.Value.ToString());
                            MySqlCommand.Parameters.Add(new MySqlParameter(item.Key, value));
                        }
                    }
                    ret = MySqlCommand.ExecuteNonQuery();
                    MySqlCommand.Dispose();
                }
            }
            catch (MySqlException ex)
            {
                throw new Exception(charset.getFromDatabase(ex.Message.ToString()), ex);
            }
            catch (Exception exc)
            {
                throw exc;
            }

            return ret;
        }

        #endregion

        #region 存储过程执行

        public DataSet ExecuteProcGetDataSet(string storedProcedureName, IDictionary<string, object> parameters = null)
        {
            DataSet result = new DataSet();
            try
            {
                using (MySqlConnection connection = new MySqlConnection(connStr))
                {
                    connection.Open();

                    MySqlCommand command = new MySqlCommand(storedProcedureName, connection);
                    MySqlParameter ac = new MySqlParameter();
                    command.CommandType = CommandType.StoredProcedure;

                    if (parameters != null)
                    {
                        foreach (var param in parameters)
                        {
                            object value = param.Value;
                            if (param.Value is String)
                                value = charset.insertToDatabase(param.Value.ToString());
                            command.Parameters.Add(new MySqlParameter(param.Key, value));
                        }
                    }

                    MySqlDataAdapter MySqlDataAdapter = new MySqlDataAdapter(command);
                    MySqlDataAdapter.Fill(result);
                    command.Dispose();
                    MySqlDataAdapter.Dispose();
                    //connection.Close();
                    //connection.Dispose();
                }

                result = (DataSet)ConvertData(result);
            }
            catch (MySqlException ex)
            {
                throw new Exception(ex.Message);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            return result;
        }

        public void ExecuteProcNonQuery(string storedProcedureName, IDictionary<string, object> parameters = null)
        {
            try
            {
                using (MySqlConnection connection = new MySqlConnection(connStr))
                {
                    connection.Open();
                    MySqlCommand command = new MySqlCommand(storedProcedureName, connection);
                    command.CommandType = CommandType.StoredProcedure;

                    if (parameters != null && parameters.Count > 0)
                    {
                        foreach (var item in parameters)
                        {
                            object value = item.Value;
                            if (item.Value is String)
                                value = charset.insertToDatabase(item.Value.ToString());
                            command.Parameters.Add(new MySqlParameter(item.Key, value));
                        }
                    }

                    command.ExecuteNonQuery();
                    command.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        
        public DataSet ExecuteProcSql(string commandText, IDictionary<string, object> parameters = null)
        {
            DataSet result = new DataSet();
            try
            {
                //检查参数与sql匹配性
                CheckBusParameters(commandText, parameters);

                string sqlStr = charset.insertToDatabase(commandText);
                using (MySqlConnection connection = new MySqlConnection(connStr))
                {
                    connection.Open();
                    MySqlCommand MySqlCommand = new MySqlCommand(sqlStr, connection);
                    if (parameters != null && parameters.Count > 0)
                    {
                        foreach (var item in parameters)
                        {
                            object value = item.Value;
                            if (item.Value is String)
                                value = charset.insertToDatabase(item.Value.ToString());
                            MySqlCommand.Parameters.Add(new MySqlParameter(item.Key, value));
                        }
                    }
                    MySqlDataAdapter MySqlDataAdapter = new MySqlDataAdapter(MySqlCommand);
                    MySqlDataAdapter.Fill(result);
                    MySqlDataAdapter.Dispose();
                    MySqlCommand.Dispose();
                }
                foreach (DataTable table in result.Tables)
                {
                    charset.getDataTableFromDatabase(table);
                }
            }
            catch (MySqlException ex)
            {
                throw new Exception(charset.getFromDatabase(ex.Message.ToString()), ex);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            return result;
        }

        /// <summary>
        /// 转化DataSet返回结果中特殊的文本字符
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        private object ConvertData(DataSet ds)
        {
            for (int c = 0; c < ds.Tables.Count; c++)
            {
                for (int i = 0; i < ds.Tables[c].Rows.Count; i++)
                {
                    for (int j = 0; j < ds.Tables[c].Columns.Count; j++)
                    {
                        if (ds.Tables[c].Rows[i][j] is String)
                            ds.Tables[c].Rows[i][j] = charset.getFromDatabase(ds.Tables[c].Rows[i][j].ToString());
                        //转换过程中可能出现的%1文本
                        if (ds.Tables[c].Rows[i][j] is String)
                        {
                            string msg = ds.Tables[c].Rows[i][j].ToString();
                            if (msg.IndexOf("%1") >= 0 && msg.IndexOf("^") >= 0)
                            {
                                msg = msg.Replace("%1", msg.Substring(msg.IndexOf("^") + 1));
                                msg = msg.Substring(0, msg.IndexOf("^"));
                            }
                            ds.Tables[c].Rows[i][j] = msg;
                        }
                    }
                }
            }
            return ds;
        }

        #endregion
        
        /// <summary>
        /// 检查并去除多余参数
        /// </summary>
        /// <param name="sqlText">将要执行的sql</param>
        /// <param name="parameters">sql参数</param>
        private void CheckBusParameters(string sqlText, IDictionary<string, object> parameters)
        {
            if (string.IsNullOrWhiteSpace(sqlText) || parameters == null)
                return;

            if (parameters.Count == 0)
                return;

            //筛选出parameters中相对sqlText多余的参数名称
            List<string> nouseParam = parameters.Where(p => !sqlText.Contains(p.Key)).Select(pr => pr.Key).ToList();
            foreach (var keyName in nouseParam)
            {
                parameters.Remove(keyName);
            }
        }
    }
}
